package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IRentVehicleInfo;
import youleche.com.model.MRentVehicleInfo;

/**
 * 租车信息访问对象
 * @author Administrator
 *
 */
public class DRentVehicleInfo implements IRentVehicleInfo{

	/**
	 * 添加租车信息
	 * @param con 数据库连接
	 * @param rentVehicleInfo 租车信息
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int addRentVehicleInfo(Connection con,
			MRentVehicleInfo rentVehicleInfo) throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "INSERT rentVehicleInfo(rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone,imageId,isDelete,rentPrice,rentAddress,shopName,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, rentVehicleInfo.getRentId());
			pre.setObject(2, rentVehicleInfo.getReleaseId());
			pre.setObject(3, rentVehicleInfo.getRentTitle());
			pre.setObject(4, rentVehicleInfo.getRentDesc());
			pre.setObject(5, rentVehicleInfo.getVehicleType());
			pre.setObject(6, rentVehicleInfo.getRentType());
			pre.setObject(7, rentVehicleInfo.getLinkMan());
			pre.setObject(8, rentVehicleInfo.getLinkPhone());
			pre.setObject(9, rentVehicleInfo.getImageId());
			pre.setObject(10, rentVehicleInfo.getIsDelete());
			pre.setObject(11, rentVehicleInfo.getRentPrice());
			pre.setObject(12, rentVehicleInfo.getRentAddress());
			pre.setObject(13, rentVehicleInfo.getShopName());
			pre.setObject(14, rentVehicleInfo.getA1());
			pre.setObject(15, rentVehicleInfo.getA2());
			pre.setObject(16, rentVehicleInfo.getA3());
			pre.setObject(17, rentVehicleInfo.getA4());
			pre.setObject(18, rentVehicleInfo.getA5());
			pre.setObject(19, rentVehicleInfo.getA6());
			pre.setObject(20, rentVehicleInfo.getA7());
			pre.setObject(21, rentVehicleInfo.getA8());
			pre.setObject(22, rentVehicleInfo.getA9());
			pre.setObject(23, rentVehicleInfo.getA10());
			pre.setObject(24, rentVehicleInfo.getA11());
			pre.setObject(25, rentVehicleInfo.getA12());
			pre.setObject(26, rentVehicleInfo.getA13());
			pre.setObject(27, rentVehicleInfo.getA14());
			pre.setObject(28, rentVehicleInfo.getA15());
			pre.setObject(29, rentVehicleInfo.getA16());
			pre.setObject(30, rentVehicleInfo.getA17());
	
			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 根据id得到租车信息
	 * @param con 数据库连接
	 * @param rentId 租车ID
	 * @return 返回租车信息
	 * @throws Exception 异常
	 */
	@Override
	public MRentVehicleInfo getRentVehicleInfoById(Connection con, String rentId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone," +
					"imageId,isDelete," +
					"rentPrice,rentAddress,shopName,A1,A2," +
					"A3,A4,A5,A6,A7," +
					"A8,A9,A10,A11,A12," +
					"A13,A14,A15,A16,A17,addTime" +
					" FROM rentVehicleInfo where rentId='"+rentId+ "' and isDelete='0'";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MRentVehicleInfo info = null;
			if(rs != null){
				while(rs.next()){
					info = new MRentVehicleInfo();
					info.setRentId(rs.getString(1));
					info.setReleaseId(rs.getString(2));
					info.setRentTitle(rs.getString(3));
					info.setRentDesc(rs.getString(4));
					info.setVehicleType(rs.getString(5));
					info.setRentType(rs.getString(6));
					info.setLinkMan(rs.getString(7));
					info.setLinkPhone(rs.getString(8));
					info.setImageId(rs.getString(9));
					info.setIsDelete(rs.getString(10));
					info.setRentPrice(rs.getInt(11));
					info.setRentAddress(rs.getString(12));
					info.setShopName(rs.getString(13));
					info.setA1(rs.getString(14));
					info.setA2(rs.getString(15));
					info.setA3(rs.getString(16));
					info.setA4(rs.getInt(17));
					info.setA5(rs.getString(18));
					info.setA6(rs.getString(19));
					info.setA7(rs.getString(20));
					info.setA8(rs.getString(21));
					info.setA9(rs.getString(22));
					info.setA10(rs.getString(23));
					info.setA11(rs.getString(24));
					info.setA12(rs.getString(25));
					info.setA13(rs.getString(26));
					info.setA14(rs.getString(27));
					info.setA15(rs.getString(28));
					info.setA16(rs.getString(29));
					info.setA17(rs.getString(30));
					info.setAddTime(rs.getTimestamp(31));
					
					return info;
				}
			}
			
			return info;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 得到用户今天发布的租车信息数量
	 * @param con 数据库连接
	 * @param releaseId 发布者id
	 * @return 返回租车信息数量
	 * @throws Exception 异常
	 */
	@Override
	public String getRentVehicleInfoByUserTodayNums(Connection con,
			String releaseId) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(rentId) from rentVehicleInfo where isdelete=0 and releaseId = '"+releaseId+"' and  DATE_FORMAT(addTime, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d')";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs !=null){
				while(rs.next()){
					return rs.getString(1);
				}
			}
			
			return "0";
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取租车信息列表
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 每次获取条数
	 * @return 返回列表信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MRentVehicleInfo> getRentVehicleInfoForList(Connection con,
			String startIndex, String endIndex, String orderBy) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone," +
					"imageId,isDelete," +
					"rentPrice,rentAddress,shopName,A1,A2," +
					"A3,A4,A5,A6,A7," +
					"A8,A9,A10,A11,A12," +
					"A13,A14,A15,A16,A17,addTime" +
					" FROM rentVehicleInfo where isDelete='0' "+ orderBy +" limit "+startIndex+","+endIndex+" ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MRentVehicleInfo> list = null;
			MRentVehicleInfo info = null;
			if(rs != null){
				list = new LinkedList<MRentVehicleInfo>();
				while(rs.next()){
					info = new MRentVehicleInfo();
					info.setRentId(rs.getString(1));
					info.setReleaseId(rs.getString(2));
					info.setRentTitle(rs.getString(3));
					info.setRentDesc(rs.getString(4));
					info.setVehicleType(rs.getString(5));
					info.setRentType(rs.getString(6));
					info.setLinkMan(rs.getString(7));
					info.setLinkPhone(rs.getString(8));
					info.setImageId(rs.getString(9));
					info.setIsDelete(rs.getString(10));
					info.setRentPrice(rs.getInt(11));
					info.setRentAddress(rs.getString(12));
					info.setShopName(rs.getString(13));
					info.setA1(rs.getString(14));
					info.setA2(rs.getString(15));
					info.setA3(rs.getString(16));
					info.setA4(rs.getInt(17));
					info.setA5(rs.getString(18));
					info.setA6(rs.getString(19));
					info.setA7(rs.getString(20));
					info.setA8(rs.getString(21));
					info.setA9(rs.getString(22));
					info.setA10(rs.getString(23));
					info.setA11(rs.getString(24));
					info.setA12(rs.getString(25));
					info.setA13(rs.getString(26));
					info.setA14(rs.getString(27));
					info.setA15(rs.getString(28));
					info.setA16(rs.getString(29));
					info.setA17(rs.getString(30));
					info.setAddTime(rs.getTimestamp(31));
					
					list.add(info);
				}
			}

			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取租车信息总条数
	 * @param con 数据库连接
	 * @return 返回租车信息总条数
	 * @throws Exception 异常
	 */
	@Override
	public int getRentInfoCount(Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(rentId) from rentVehicleInfo where isdelete=0";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			int result = 0;
			if(rs != null){
				while(rs.next()){
					result = rs.getInt(1);
					break;
				}
			}
			
			return result;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据租车id更新图片信息
	 * @param con 数据库连接
	 * @param imageId 图片ID
	 * @param rentId 租车信息id
	 * @return 返回更新影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int updateImageIdByRentId(Connection con, String imageId, String rentId)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update rentVehicleInfo set imageId ='"+imageId+"' where isdelete=0 and rentId='"+rentId+"' limit 1;";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**根据用户名得到租车信息总条数
	 * 
	 * @param con 数据库连接
	 * @param userName 用户名
	 * @return 返回租车信息总条数
	 * @throws Exception 异常
	 */
	@Override
	public int getRentInfoCountByUserName(Connection con, String userName)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(rentId) from rentVehicleInfo where isdelete=0 and a3='"+userName+"'";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			int result = 0;
			if(rs != null){
				while(rs.next()){
					result = rs.getInt(1);
					break;
				}
			}
			
			return result;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据用户名获取租车信息列表
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 每次获取条数
	 * @param userName 用户名
	 * @return 返回列表信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MRentVehicleInfo> getRentVehicleInfoForListByUserName(
			Connection con, String startIndex, String endIndex, String userName)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone," +
					"imageId,isDelete," +
					"rentPrice,rentAddress,shopName,A1,A2," +
					"A3,A4,A5,A6,A7," +
					"A8,A9,A10,A11,A12," +
					"A13,A14,A15,A16,A17,addTime" +
					" FROM rentVehicleInfo where isDelete='0' and a3='"+userName+"' order by addTime desc limit "+startIndex+","+endIndex+"";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MRentVehicleInfo> list = null;
			MRentVehicleInfo info = null;
			if(rs != null){
				list = new LinkedList<MRentVehicleInfo>();
				while(rs.next()){
					info = new MRentVehicleInfo();
					info.setRentId(rs.getString(1));
					info.setReleaseId(rs.getString(2));
					info.setRentTitle(rs.getString(3));
					info.setRentDesc(rs.getString(4));
					info.setVehicleType(rs.getString(5));
					info.setRentType(rs.getString(6));
					info.setLinkMan(rs.getString(7));
					info.setLinkPhone(rs.getString(8));
					info.setImageId(rs.getString(9));
					info.setIsDelete(rs.getString(10));
					info.setRentPrice(rs.getInt(11));
					info.setRentAddress(rs.getString(12));
					info.setShopName(rs.getString(13));
					info.setA1(rs.getString(14));
					info.setA2(rs.getString(15));
					info.setA3(rs.getString(16));
					info.setA4(rs.getInt(17));
					info.setA5(rs.getString(18));
					info.setA6(rs.getString(19));
					info.setA7(rs.getString(20));
					info.setA8(rs.getString(21));
					info.setA9(rs.getString(22));
					info.setA10(rs.getString(23));
					info.setA11(rs.getString(24));
					info.setA12(rs.getString(25));
					info.setA13(rs.getString(26));
					info.setA14(rs.getString(27));
					info.setA15(rs.getString(28));
					info.setA16(rs.getString(29));
					info.setA17(rs.getString(30));
					info.setAddTime(rs.getTimestamp(31));
					
					list.add(info);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据租车ID删除租车信息
	 * @param con 数据库连接
	 * @param id Id
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int deleteRentInfoById(Connection con, String id) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "delete from rentVehicleInfo where isdelete=0 and rentId='"+id+"' limit 1;";
			
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 根据id更新相关度（访问量）
	 * @param con 数据连接
	 * @param id id
	 * @param a4 相关度
	 * @return 返回更新影响的行数
	 * @throws exception 异常
	 */
	@Override
	public int updateA4ById(Connection con, String id, int a4)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update rentVehicleInfo set a4 =" + a4 + " where isdelete=0 and rentId='" + id + "' limit 1;";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 根据id得到推荐租车信息
	 * @param con 数据库连接
	 * @return 返回租车信息
	 * @throws Exception 异常
	 */
	@Override
	public MRentVehicleInfo getRecommendRentVehicleInfo(Connection con)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone," +
					"imageId,isDelete," +
					"rentPrice,rentAddress,shopName,A1,A2," +
					"A3,A4,A5,A6,A7," +
					"A8,A9,A10,A11,A12," +
					"A13,A14,A15,A16,A17,addTime" +
					" FROM rentvehicleinfo where imageId != '0' and isDelete='0' order by addTime desc limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MRentVehicleInfo info = null;
			if(rs != null){
				while(rs.next()){
					info = new MRentVehicleInfo();
					info.setRentId(rs.getString(1));
					info.setReleaseId(rs.getString(2));
					info.setRentTitle(rs.getString(3));
					info.setRentDesc(rs.getString(4));
					info.setVehicleType(rs.getString(5));
					info.setRentType(rs.getString(6));
					info.setLinkMan(rs.getString(7));
					info.setLinkPhone(rs.getString(8));
					info.setImageId(rs.getString(9));
					info.setIsDelete(rs.getString(10));
					info.setRentPrice(rs.getInt(11));
					info.setRentAddress(rs.getString(12));
					info.setShopName(rs.getString(13));
					info.setA1(rs.getString(14));
					info.setA2(rs.getString(15));
					info.setA3(rs.getString(16));
					info.setA4(rs.getInt(17));
					info.setA5(rs.getString(18));
					info.setA6(rs.getString(19));
					info.setA7(rs.getString(20));
					info.setA8(rs.getString(21));
					info.setA9(rs.getString(22));
					info.setA10(rs.getString(23));
					info.setA11(rs.getString(24));
					info.setA12(rs.getString(25));
					info.setA13(rs.getString(26));
					info.setA14(rs.getString(27));
					info.setA15(rs.getString(28));
					info.setA16(rs.getString(29));
					info.setA17(rs.getString(30));
					info.setAddTime(rs.getTimestamp(31));
					
					return info;
				}
			}
			
			return info;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取下一条车辆信息
	 * @param con 数据库连接
	 * @param addTime 本条车辆信息添加时间
	 * @return 返回租车信息
	 * @throws Exception 异常
	 */
	@Override
	public MRentVehicleInfo getNextVehicleInfo(Connection con, String addTime)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone," +
					"imageId,isDelete," +
					"rentPrice,rentAddress,shopName,A1,A2," +
					"A3,A4,A5,A6,A7," +
					"A8,A9,A10,A11,A12," +
					"A13,A14,A15,A16,A17,addTime" +
					" FROM rentvehicleinfo where addTime<'" + addTime + "' order by addTime desc limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MRentVehicleInfo info = null;
			if(rs != null){
				while(rs.next()){
					info = new MRentVehicleInfo();
					info.setRentId(rs.getString(1));
					info.setReleaseId(rs.getString(2));
					info.setRentTitle(rs.getString(3));
					info.setRentDesc(rs.getString(4));
					info.setVehicleType(rs.getString(5));
					info.setRentType(rs.getString(6));
					info.setLinkMan(rs.getString(7));
					info.setLinkPhone(rs.getString(8));
					info.setImageId(rs.getString(9));
					info.setIsDelete(rs.getString(10));
					info.setRentPrice(rs.getInt(11));
					info.setRentAddress(rs.getString(12));
					info.setShopName(rs.getString(13));
					info.setA1(rs.getString(14));
					info.setA2(rs.getString(15));
					info.setA3(rs.getString(16));
					info.setA4(rs.getInt(17));
					info.setA5(rs.getString(18));
					info.setA6(rs.getString(19));
					info.setA7(rs.getString(20));
					info.setA8(rs.getString(21));
					info.setA9(rs.getString(22));
					info.setA10(rs.getString(23));
					info.setA11(rs.getString(24));
					info.setA12(rs.getString(25));
					info.setA13(rs.getString(26));
					info.setA14(rs.getString(27));
					info.setA15(rs.getString(28));
					info.setA16(rs.getString(29));
					info.setA17(rs.getString(30));
					info.setAddTime(rs.getTimestamp(31));
					
					return info;
				}
			}
			
			return info;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取上一条车辆信息
	 * @param con 数据库连接
	 * @param addTime 本条车辆信息添加时间
	 * @return 返回租车信息
	 * @throws Exception 异常
	 */
	@Override
	public MRentVehicleInfo getPreVehicleInfo(Connection con, String addTime)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT rentId,releaseId,rentTitle,rentDesc,vehicleType,rentType,linkMan,linkPhone," +
					"imageId,isDelete," +
					"rentPrice,rentAddress,shopName,A1,A2," +
					"A3,A4,A5,A6,A7," +
					"A8,A9,A10,A11,A12," +
					"A13,A14,A15,A16,A17,addTime" +
					" FROM rentvehicleinfo where addTime>'" + addTime + "' order by addTime desc limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MRentVehicleInfo info = null;
			if(rs != null){
				while(rs.next()){
					info = new MRentVehicleInfo();
					info.setRentId(rs.getString(1));
					info.setReleaseId(rs.getString(2));
					info.setRentTitle(rs.getString(3));
					info.setRentDesc(rs.getString(4));
					info.setVehicleType(rs.getString(5));
					info.setRentType(rs.getString(6));
					info.setLinkMan(rs.getString(7));
					info.setLinkPhone(rs.getString(8));
					info.setImageId(rs.getString(9));
					info.setIsDelete(rs.getString(10));
					info.setRentPrice(rs.getInt(11));
					info.setRentAddress(rs.getString(12));
					info.setShopName(rs.getString(13));
					info.setA1(rs.getString(14));
					info.setA2(rs.getString(15));
					info.setA3(rs.getString(16));
					info.setA4(rs.getInt(17));
					info.setA5(rs.getString(18));
					info.setA6(rs.getString(19));
					info.setA7(rs.getString(20));
					info.setA8(rs.getString(21));
					info.setA9(rs.getString(22));
					info.setA10(rs.getString(23));
					info.setA11(rs.getString(24));
					info.setA12(rs.getString(25));
					info.setA13(rs.getString(26));
					info.setA14(rs.getString(27));
					info.setA15(rs.getString(28));
					info.setA16(rs.getString(29));
					info.setA17(rs.getString(30));
					info.setAddTime(rs.getTimestamp(31));
					
					return info;
				}
			}
			
			return info;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

}
